﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class ChuyenThuDAO
    {
        public List<ChuyenThuDTO> LayThongTinChuyenThu()
        {
            string sql = "SP_LietKeThongTinChuyenThu ";

            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<ChuyenThuDTO> DanhSach = new List<ChuyenThuDTO>();
            while (reader.Read())
            {
                ChuyenThuDTO ctDTO = new ChuyenThuDTO();
                ctDTO.MaDH = reader.GetString(0);
                ctDTO.MaMA = reader.GetString(1);
                ctDTO.NgayGioTH = reader.GetDateTime(2);
                ctDTO.MATT = reader.GetInt32(3);
                ctDTO.MaNVTH = reader.GetString(4);
                ctDTO.SLHopThu = reader.GetInt32(5);
                ctDTO.SLHopThieu = reader.GetInt32(6);
                DanhSach.Add(ctDTO);
            }
            conn.Close();
            return DanhSach;
        }

        public List<ChuyenThuDTO> LietKeDonHangDangThu()
        {
            string sql = "SP_LietKeDonHangDangThu";

            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<ChuyenThuDTO> DanhSach = new List<ChuyenThuDTO>();
            while (reader.Read())
            {
                ChuyenThuDTO ctDTO = new ChuyenThuDTO();
                ctDTO.MaDH = reader.GetString(0);
                ctDTO.MaMA = reader.GetString(1);
                ctDTO.NgayGioTH = reader.GetDateTime(2);
                ctDTO.MATT = reader.GetInt32(3);
                ctDTO.MaNVTH = reader.GetString(4);
                ctDTO.SLHopThu = reader.GetInt32(5);
                ctDTO.SLHopThieu = reader.GetInt32(6);
                DanhSach.Add(ctDTO);
            }
            conn.Close();
            return DanhSach;
        }

        public List<ChuyenThuDTO> LietKeDonHangHoanTat()
        {
            string sql = "SP_LietKeDonHangHoanTat";

            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<ChuyenThuDTO> DanhSach = new List<ChuyenThuDTO>();
            while (reader.Read())
            {
                ChuyenThuDTO ctDTO = new ChuyenThuDTO();
                ctDTO.MaDH = reader.GetString(0);
                ctDTO.MaMA = reader.GetString(1);
                ctDTO.NgayGioTH = reader.GetDateTime(2);
                ctDTO.MATT = reader.GetInt32(3);
                ctDTO.MaNVTH = reader.GetString(4);
                ctDTO.SLHopThu = reader.GetInt32(5);
                ctDTO.SLHopThieu = reader.GetInt32(6);
                DanhSach.Add(ctDTO);
            }
            conn.Close();
            return DanhSach;
        }

        public int CapNhatSoHopThieu(ChuyenThuDTO ctDTO)
        {
            string sql = "SP_CapNhatChuyenThu '" + ctDTO.MaDH + "','" + ctDTO.MaMA + "','" + ctDTO.NgayThu + "','" + ctDTO.MaNVTH +"'," + ctDTO.SLHopThu +',' + ctDTO.SLHopThieu;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }

        public int ThemChuyenThu(ChuyenThuDTO ctDTO)
        {
            string sql = "SP_ThemChuyenThu '" + ctDTO.MaDH + "','" + ctDTO.MaMA + "','" + ctDTO.NgayThu + "','" + ctDTO.MaNVTH + "'," + ctDTO.SLHopThu + ',' + ctDTO.SLHopThieu;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }

        public List<ChuyenThuDTO> LietKeDonHangTheoNgay(ChuyenThuDTO ct)
        {
            string sql = "SP_LietKeDonHangTheoNgay " + "'" + ct.NgayBatDau + "','" + ct.NgayKetThuc +"'"  ;

            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<ChuyenThuDTO> DanhSach = new List<ChuyenThuDTO>();
            while (reader.Read())
            {
                ChuyenThuDTO ctDTO = new ChuyenThuDTO();
                ctDTO.MaDH = reader.GetString(0);
                ctDTO.MaMA = reader.GetString(1);
                ctDTO.NgayGioTH = reader.GetDateTime(2);
                ctDTO.MATT = reader.GetInt32(3);
                ctDTO.MaNVTH = reader.GetString(4);
                ctDTO.SLHopThu = reader.GetInt32(5);
                ctDTO.SLHopThieu = reader.GetInt32(6);
                DanhSach.Add(ctDTO);
            }
            conn.Close();
            return DanhSach;
        }
    }
}
